
package com.be.beadmin.project.repository;

import com.be.beadmin.project.entity.BatchPlan;
import com.be.beadmin.project.entity.BatchProduct;
import io.swagger.models.auth.In;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

/**
* @author zjm
* @date 2023-02-13
**/
public interface BatchProductRepository extends JpaRepository<BatchProduct, String>, JpaSpecificationExecutor<BatchProduct> {


    BatchProduct findBatchProductByProductCode(String productCode);
    BatchProduct findBatchProductByProductCodeAndDelete(String productCode,int delete);

    BatchProduct findBatchProductByIdAndDelete(String id, Integer delete);

    List<BatchProduct> findBatchProductByInfoIdAndReviewStateAndDelete(String infoId, Integer reviewState, Integer delete);

    int countBatchProductsByInfoIdAndBatchPlanIdAndDelete(String infoId,String batchPlanId, Integer delete);

    List<BatchProduct> findBatchProductsByIdInAndDelete(List<String> id, Integer delete);

    BatchProduct findBatchProductById(String id);

    List<BatchProduct> findBatchProductByBatchPlanIdInAndDelete(List<String> batchPlanId,int delete);

    List<BatchProduct> findBatchProductByBatchPlanIdAndDelete(String batchPlanId,int delete);

    List<BatchProduct> findBatchProductByInfoIdAndBatchPlanIdAndDelete(String infoId,String batchPlanId,int delete);

    List<BatchProduct> findBatchProductByInfoIdAndBatchPlanIdAndDeleteOrderByCreateTimeDesc(String infoId,String batchPlanId,int delete);

    List<BatchProduct> findBatchProductByBatchPlanIdAndProductTypeAndIsStandardAndDelete(String batchPlanId,String productType,int isStandard, int delete);


    List<BatchProduct> findBatchProductByInfoIdAndDelete(String batchPlanId,int delete);

    int countBatchProductByBatchPlanIdInAndProductTypeAndDelete(List<String> batchPlanId,String productType, int delete);



    int countBatchProductsByBatchPlanIdAndProductTypeAndDelete(String batchPlanId,String productType, int delete);

    int countBatchProductsByBatchPlanIdAndDelete(String batchPlanId, int delete);

    int countBatchProductByBatchPlanIdAndProductTypeAndIsStandardAndDelete(String batchPlanId,String productType,int isStandard, int delete);

    int countBatchProductByBatchPlanIdAndProductTypeAndUnstandardNameAndIsStandardAndDelete(String batchPlanId,String productType,String unstandardName,int isStandard, int delete);


    @Query(value = "SELECT distinct bp.unstandard_name from t_pm_batch_product bp where bp.is_standard = ?1 and bp.batch_plan_id = ?2 and bp.is_delete = ?3",nativeQuery = true)
    List<String> findUnstandardNameByIsStandardAndBatchPlanIdAndDelete(int isStandard,String batch_plan_id,int delete);



    @Modifying
    @Transactional
    @Query(value = "update t_pm_batch_product bp set bp.batch_plan_id = ?1 where bp.info_id = ?2 and bp.is_delete = ?3",nativeQuery = true)
    void updateBatchPlanIdByInfoIdAndDelete(String batchPlanId,String infoId,int delete);



    @Query(value = "select bp.id from t_pm_batch_product bp where bp.batch_plan_id = ?1 and bp.is_delete = ?2",nativeQuery = true)
    List<String> getBatchProductIdsByBatchPlanIdAndDelete(String batchPlanId,int delete);

    @Modifying
    @Transactional
    @Query(value = "update t_pm_batch_product bp set bp.is_delete = ?1 where bp.info_id = ?2 and bp.is_delete = ?3",nativeQuery = true)
    void deleteBatchProductByInfoIdAndDelete(int deleted,String infoId,int nodelete);


    @Modifying
    @Transactional
    @Query(value = "update t_pm_batch_product bp set bp.batch_plan_id = ?1 where bp.batch_plan_id = ?2 and bp.is_delete = ?3",nativeQuery = true)
    void updateBatchPlanIdByBatchPlanIdAndDelete(String newbatchPlanId,String oldbatchPlanId,int delete);

    @Modifying
    @Transactional
    @Query(value = "update t_pm_batch_product bp set bp.batch_delivery_date = ?1 where bp.info_id = ?2 and bp.is_delete = ?3",nativeQuery = true)
    void updateBatchDeliveryDateByInfoIdAndDelete(Date batchDeliveryDate ,String infoId,int delete);

    @Modifying
    @Transactional
    @Query(value = "update t_pm_batch_product bp set bp.batch_count = ?1 where bp.master_plan_id = ?2 and bp.is_delete = ?3",nativeQuery = true)
    void updateBatchCountByMasterPlanIdIdAndDelete(int batchCount ,String masterPlanId,int delete);
//    @Modifying
//    @Transactional
//    @Query(value = "select t_pm_batch_product bp where bp.product_type = ?1 and bp.info_id = ?2 and bp.is_delete = ?3",nativeQuery = true)
    List<BatchProduct> findBatchProductByProductTypeAndInfoIdAndDelete(String productType, String infoId, int delete);

    List<BatchProduct> findBatchProductByProductTypeAndUnstandardNameAndInfoIdAndDelete(String productType, String unstandardName,String infoId, int delete);

    List<BatchProduct> findBatchProductByProductTypeAndInfoIdAndBatchDeliveryDateAndDelete(String productType, String infoId, Date deliverDate,  int delete);

    List<BatchProduct> findBatchProductByProductTypeAndInfoIdAndBatchDeliveryDateAfterAndDelete(String productType, String infoId, Date deliverDate, int delete);

    List<BatchProduct> findBatchProductByProductTypeAndInfoIdAndBatchDeliveryDateBeforeAndDelete(String productType, String infoId, Date deliverDate, int delete);

    List<BatchProduct> findBatchProductByProductTypeAndUnstandardNameAndInfoIdAndBatchDeliveryDateAndDelete(String productType, String unstandardName,String infoId, Date deliverDate,int delete);

    List<BatchProduct> findBatchProductByProductTypeAndUnstandardNameAndInfoIdAndBatchDeliveryDateAfterAndDelete(String productType, String unstandardName,String infoId, Date deliverDate,int delete);

    List<BatchProduct> findBatchProductByProductTypeAndUnstandardNameAndInfoIdAndBatchDeliveryDateBeforeAndDelete(String productType, String unstandardName,String infoId, Date deliverDate,int delete);

    @Query(value = "select bp.id from t_pm_batch_product bp where bp.batch_plan_id = ?1 and bp.is_delete = ?2",nativeQuery = true)
    List<String> findBatchProductIdsByWoIdsAndDelete(List<String> woIds, int i);


    @Query(value = "SELECT distinct bp.batch_plan_id from t_pm_batch_product bp where bp.id in ?1 and bp.is_delete = ?2",nativeQuery = true)
    List<String> findBatchPlanIdsByBatchProductIdsAndDelete(List<String> batchProductIds,int delete);

    List<BatchProduct> findBatchProductByStandardProductCodeAndStateAndDelete(String standTypeCode, int state, int delete);

    @Query(value = "select count(*) from BatchProduct bpd left join ProductManufactInfor pmi on bpd.id = pmi.batchProductId where bpd.batchPlanId = (select bp.batchPlanId from BatchProduct bp where bp.id = ?1 and bp.delete = 0) and pmi.processStatus != 0 and bpd.delete = 0 and pmi.delete = 0")
    int countExecutedProduct(String batchproductId);

    @Query(value = "select bp.id from BatchPlan bp left join BatchProduct bpd on bp.id = bpd.batchPlanId where bpd.id = ?1 and bp.delete = 0 and bpd.delete = 0")
    String findBatchPlanByBatchProductId(String batchProductId);
    @Query(value = "select count(*) from BatchProduct bpd left join ProductManufactInfor pmi on bpd.id = pmi.batchProductId where bpd.batchPlanId = (select bp.batchPlanId from BatchProduct bp where bp.id = ?1 and bp.delete = 0) and pmi.processStatus != 3 and bpd.id != ?1 and bpd.delete = 0 and pmi.delete = 0")
    int countUnFinishExecutedProductExceptThis(String batchProductId);
}